package dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import bean.DBHelper;
import bean.Question;
import bean.Student;

public class StudentDao {
	public boolean checkID(String ID,String pwd)
	{
		boolean sign=false;
		String sql="select spwd from student where sno="+ID;
		ResultSet rs=DBHelper.executeQuery(sql);
		try{
			while(rs.next()){
				if(pwd.equals(rs.getString(1)))
				{
					sign=true;
				}
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		DBHelper.close();
		return sign;
	}
	public List<Student> allStudent() {
		List<Student> list = new ArrayList<Student>();
		String sql = "select sno,sname,spwd,ssex,mailbox,grade from student";
		ResultSet rs = DBHelper.executeQuery(sql);
        try{
		while (rs.next()) {
			Student stu = new Student();
				stu.setSno(rs.getString(1));
				stu.setSname(rs.getString(2));
				stu.setSpwd(rs.getString(3));
				stu.setSsex(rs.getString(4));
				stu.setMailbox(rs.getString(5));
				stu.setGrade(rs.getString(6));				
				list.add(stu);
			}
        } 
        catch (Exception ex) 
        {
			ex.printStackTrace();
		}
        DBHelper.close();
		return list;
	}
	public String getStudentName(String id){
		String name=null;
		String sql="select sname from student where sno="+id;
		ResultSet rs=DBHelper.executeQuery(sql);
		try{
			while(rs.next()){
				name=rs.getString(1);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
		DBHelper.close();
		return name;
	}
	public boolean addStudent(Student stu){
		String sql=null;
		boolean sign=false;
		try{
			sql="insert into student(sno,sname,spwd,ssex,mailbox,grade) values('"+stu.getSno()+"','"+stu.getSname()
					+"','"+stu.getSpwd()+"','"+stu.getSsex()+"','"+stu.getMailbox()+"','"+stu.getGrade()+"')";
			DBHelper.executeUpdate(sql);
			sign=true;
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		DBHelper.close();
		return sign;
	}
	public boolean deleteStudentBySno(String sno){
		String sql=null;
		try{
			sql="delete from student where sno='"+sno+"'";
			DBHelper.executeUpdate(sql);
			return false;		
		}catch(Exception ex){
			ex.printStackTrace();
		}
		DBHelper.close();
		return false;
	}
	public boolean updateStudent(String sno,String sname,String spwd,String ssex,String mailbox,String grade){
		String sql=null;
		boolean sign=false;
		try{
			sql="update student set sno="+sno+"sname="+sname+"spwd="+spwd+"ssex="+ssex+"mailbox="+mailbox+"grade="+grade;
			DBHelper.executeUpdate(sql);
			sign=true;
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		DBHelper.close();
		return sign;
	}
	public List <Student> allScore() {
		List <Student> list = new ArrayList<Student>();
		String sql = "select sno,sname,score from student";
		ResultSet rs = DBHelper.executeQuery(sql);
        try{
		while (rs.next()) {
			    Student stu = new Student();
				stu.setSno(rs.getString(1));
				stu.setSname(rs.getString(2));
				stu.setScore(rs.getString(3));				
				list.add(stu);
			}
        } 
        catch (Exception ex) 
        {
			ex.printStackTrace();
		}
        DBHelper.close();
		return list;
	}
	public static void main(String ags[]){
		
	}
	public ArrayList<Student> getOnePage(int position,int pageSize) {
		ArrayList<Student> list = new ArrayList<Student>();
		String sql = "select * from student";
		ResultSet rs = DBHelper.executeQuery(sql);
		 try{
			 
				for(int i=0;i<position;i++){
					rs.next();
				}
				for(int j=0;j<pageSize;j++){
					Student stu = new Student();
					stu.setSno(rs.getString(1));
					stu.setSname(rs.getString(2));
					stu.setSpwd(rs.getString(3));
					stu.setSsex(rs.getString(4));
					stu.setMailbox(rs.getString(5));
					stu.setGrade(rs.getString(6));				
					list.add(stu);
					rs.next();
				}
		        } 
		        catch (Exception ex) 
		        {
					ex.printStackTrace();
				}
		        DBHelper.close();
				return list;
	}
	public ArrayList<Student> getLastPage(int position) {
		ArrayList<Student> list = new ArrayList<Student>();
		String sql = "select * from student";
		ResultSet rs = DBHelper.executeQuery(sql);
		 try{
			 
				for(int i=0;i<position;i++){
					rs.next();
				}
				do{
					Student stu = new Student();
					stu.setSno(rs.getString(1));
					stu.setSname(rs.getString(2));
					stu.setSpwd(rs.getString(3));
					stu.setSsex(rs.getString(4));
					stu.setMailbox(rs.getString(5));
					stu.setGrade(rs.getString(6));	
					list.add(stu);
					}while (rs.next()) ;
		        } 
		        catch (Exception ex) 
		        {
					ex.printStackTrace();
				}
		        DBHelper.close();
				return list;
	}
}
